/* Keep record of files changed in a text file
     Change from using Companies table to suppliers table
 */

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using StillVoiceCommon;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Collections.Generic;

/// <summary>
/// Summary description for Supplier
/// </summary>

namespace TeamTime.Classes
{

    public class Supplier : StillVoiceCommon.DataTableRow
    {
        #region properties
        private string supplierService;
        public string SupplierService
        {
            get { return supplierService; }
            set { supplierService = value; }
        }
        private string companyName;
        public string CompanyName
        {
            get { return companyName; }
            set { companyName = value; }
        }

        
        private string usernameModifiedBy;

        public new string UserNameModifiedBy
        {
            get { return usernameModifiedBy; }
            set { usernameModifiedBy = value; }
        }
        #endregion

        public Supplier()
        {
            this.initClass(TN5Y.Utilities.DBConnectionString, "Supplier");
        }
        public new void load(string id)
        {
            base.load(id);
            System.Data.SqlClient.SqlDataReader dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, 
                "select * from ViewSuppliers where ID = @id",
                new SqlParameter("id",id));
            if (dr.Read())
            {
                supplierService = dr["SupplierService"].ToString();
                usernameModifiedBy = dr["UserNameModifiedBy"].ToString();
                companyName = dr["CompanyName"].ToString();
            }
            dr.Close();
            dr = null;
        }

      
    }

    public class Suppliers
    {
        public DataSet getSupplierServices(bool anyOption)
        {
            string sql = "select ID, Description  from SupplierService";
            if (anyOption) sql = "select 0 as ID,'<Any>' as Description UNION " + sql;
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
        }

        public DataSet searchSuppliers(string searchFor, bool searchInNotes, string supplierType, string sortExpression) //, string fromDate, string toDate, 
        {
            StringBuilder sql = new StringBuilder("SELECT * FROM ViewSuppliers where 1=1 ");
            if (searchFor != "")
            {
                sql.Append(" and (CompanyName like '%'+@SearchFor+'%' or");
                sql.Append(" ID like '%'+@SearchFor+'%'");
                if (searchInNotes)
                {
                    sql.Append(" or");
                    sql.Append(" Notes like '%'+@SearchFor+'%' ");
                }


                sql.Append(") ");
            }
            if (supplierType != "0")
            {
                sql.Append(" and SupplierServiceID = @SupplierType ");
            }

            if (sortExpression != "")
            {
                sql.Append(" order by " + sortExpression);
            }
            return SqlHelper.ExecuteDataset(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql.ToString(),
                new SqlParameter("SearchFor", searchFor),
                new SqlParameter("SupplierType", supplierType));
        }



        public bool duplicateSupplierExists(string SupplierName, string ID)
        {
            bool ret = false;
            string sql = "select ID from ViewSuppliers where CompanyName = '" + SupplierName + "' ";
            if (ID != "") { sql += " and ID <> '" + ID + "'"; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql);
            ret = dr.Read();
            dr.Close();
            return ret;
        }

        public int getDuplicateSupplierID(string SupplierName, string ID)
        {
            int ret = 0;
            string sql = "select ID from ViewSuppliers where CompanyName = @SupplierName ";
            if (ID != "") { sql += " and ID <> @ID "; }//same name different id
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql,
                new SqlParameter("SupplierName", SupplierName),
                new SqlParameter("ID", ID));
            if (dr.Read())
            {
                ret = Convert.ToInt32(dr[0]);
            }
            dr.Close();
            return ret;
        }

        internal string GetSupplierEmail(Int32 SupplierID)
        {
            string ret = "";
            string sql = "select EmailAddress from ViewSuppliers where ID = @SupplierID";

            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader(TN5Y.Utilities.DBConnectionString, CommandType.Text, sql, 
                new SqlParameter("SupplierID", SupplierID));
            if (dr.Read())
            {
                ret = dr[0].ToString();
            }
            dr.Close();
            return ret;
        }

    }
}
